﻿using Dapper;
using db_manage.model;
using db_manage.model.db;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;

namespace db_manage.dll.db
{
    public class db_sqlserver : db_base
    {
        private HashSet<string> bak_dir_set = new HashSet<string>();//记录已经打开的远程目录
        public override void attach(db_info db_info, string path)
        {
            var default_db_info = new db_info() { db_link_info = db_info.db_link_info };//切换为默认数据库连接
            //获取目录下所有文件
            var list = Directory.GetFiles(path, "*.mdf", SearchOption.AllDirectories).ToList();
            if (list != null && list.Any())
            {
                list.ForEach(x =>
                {
                    //查询文件目录
                    var dbName = Path.GetFileNameWithoutExtension(x);
                    //附加数据库
                    var nowCount = sql_query<int>(default_db_info, $"select count(1) from sys.databases where name = '{dbName}'").FirstOrDefault();
                    if (nowCount > 0)
                    {
                        return;
                    }
                    sql_exec(default_db_info, ($"exec sp_attach_db '{dbName}','{x}','{(x.Replace(".mdf", "_log.ldf"))}'"));
                    sql_exec(default_db_info, $"ALTER DATABASE {dbName} SET  READ_WRITE");//非windows登录的附加会是只读状态,此处更改为读写
                });
            }
        }

        public override string bak(db_info db_info, db_bak_param db_bak_param)
        {
            if (!string.IsNullOrEmpty(db_bak_param.dir_username))
            {
                lock (bak_dir_set)
                {
                    if (!bak_dir_set.Contains(db_info.db_link_info.tip))
                    {
                        //第一次则配置远程目录
                        var dirsql = @$"
EXEC sp_configure'show advanced options', 1
RECONFIGURE
EXEC sp_configure'xp_cmdshell', 1
RECONFIGURE
EXEC sp_configure'show advanced options', 0
RECONFIGURE
EXEC master..xp_cmdshell 'net use {db_bak_param.dir_bak} /delete'
EXEC master..xp_cmdshell 'net use {db_bak_param.dir_bak} {db_bak_param.dir_pwd} /user:{db_bak_param.dir_username}'";
                        sql_exec(db_info, dirsql);
                        bak_dir_set.Add(db_info.db_link_info.tip);
                    }
                }
            }
            var bak_name = $"{db_info.name}_{(db_bak_param.bak_diff ? "diff" : "full")}_{DateTime.Now:yyyyMMddHHmmss}.bak";
            var bak_sql = $"BACKUP DATABASE {db_info.name} TO DISK='{db_bak_param.dir_bak}\\{bak_name}' WITH {(db_bak_param.bak_diff ? "DIFFERENTIAL, " : "")}NOINIT";
            sql_exec(db_info, bak_sql);
            return bak_name;
        }

        public override DbConnection db_connection_get(db_info db_info)
        {
            if (string.IsNullOrEmpty(db_info.name))
            {
                db_info.name = "master";
            }
            var connStr = $"Server={db_info.db_link_info.ip}; Database={db_info.name}; User ID={db_info.db_link_info.username}; Password={db_info.db_link_info.pwd}";
            return new SqlConnection(connStr);
        }

        public override List<db_info> db_info_list(db_link_info db_link_info)
        {
            //获取系统库之前的所有数据库
            var list = sql_query<db_info>(new db_info() { db_link_info = db_link_info }, @"select 
                            NAME name
                            FROM MASTER.DBO.SYSDATABASES where dbid>6 
                            order by name asc ").AsList();
            list?.ForEach(x => x.db_link_info = db_link_info);
            return list;
        }

        public override void db_record_create(db_info db_info)
        {
            //创建执行记录的表
            var db_record_create_sql = @"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[db_manage_record]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[db_manage_record](
	[vernum] [INT] NULL,
	[updatetime] [DATETIME] NULL,
	[updatestatus] [INT] NULL,
	[remark] [VARCHAR](MAX) NULL,
	[taketime] [INT] NULL,
	[sqlstr] [VARCHAR](MAX) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
";
            sql_exec(db_info, db_record_create_sql);
        }

        public override db_struct db_struct_get(db_info db_info, bool is_sql = false)
        {
            var db_struct = new db_struct();
            // 获取表信息脚本
            var db_table_col_sql = @"
select  tablename = d.name ,
        tablenamedesc = isnull(f.value, '') ,
        colnum = a.colorder ,
        colname = a.name ,
        istip = columnproperty(a.id, a.name, 'isidentity') ,
        iskey = case when exists ( select   1
                                   from     sysobjects
                                   where    xtype = 'pk'
                                            and parent_obj = a.id
                                            and name in (
                                            select  name
                                            from    sysindexes
                                            where   indid in (
                                                    select  indid
                                                    from    sysindexkeys
                                                    where   id = a.id
                                                            and colid = a.colid ) ) )
                     then 1
                     else 0
                end ,
        coltype = b.name ,
        colbytelength = a.length ,
        collength = columnproperty(a.id, a.name, 'precision') ,
        colsclength = isnull(columnproperty(a.id, a.name, 'scale'), 0) ,
        colisnull = case when a.isnullable = 1 then 1
                         else 0
                    end ,
        coldefault = isnull(e.text, '') ,
        coldesc = isnull(g.[value], '') ,
        colconstraint = sdc.name
from    syscolumns a
        left join systypes b on a.xusertype = b.xusertype
        inner join sysobjects d on a.id = d.id
                                   and d.xtype = 'u'
                                   and d.name <> 'dtproperties'
        left join syscomments e on a.cdefault = e.id
        left join sys.extended_properties g on a.id = g.major_id
                                               and a.colid = g.minor_id
        left join sys.extended_properties f on d.id = f.major_id
                                               and f.minor_id = 0
        left join sys.default_constraints sdc on d.id = sdc.[parent_object_id]
                                                 and a.colid = sdc.parent_column_id
order by a.id ,
        a.colorder;
";
            db_struct.db_table_col_info_list = sql_query<db_table_col_info>(db_info, db_table_col_sql).ToList();
            //表列表
            db_struct.table_list = db_struct.db_table_col_info_list?.Select(x => x.tablename).Distinct().ToList() ?? new List<string>();
            // 获取视图信息脚本
            var db_view_col_sql = @"
select 
    distinct
    tablename       =  d.name,
    colname     = a.name
from 
    syscolumns a
inner join 
    sysobjects d 
on 
    a.id=d.id  and d.xtype='v' and  d.name<>'dtproperties'
";
            db_struct.db_view_col_info_list = sql_query<db_table_col_info>(db_info, db_view_col_sql).ToList();
            db_struct.view_list = db_struct.db_view_col_info_list?.Select(x => x.tablename).Distinct().ToList() ?? new List<string>();
            db_struct.view_col_dic = db_struct.db_view_col_info_list.ToDictionary(x => x.tablename + "__" + x.colname, x => x.tablename);
            if (is_sql)
            {
                //建立表的sql
                db_struct.table_create_sql_dic = new Dictionary<string, string>();
                db_struct.table_list?.ForEach(x =>
                {
                    var create_sql = $"CREATE TABLE [dbo].[{x}](";
                    var nowtemplateDBTableCol = db_struct.db_table_col_info_list.Where(y => y.tablename == x).ToList();
                    nowtemplateDBTableCol.ForEach(y =>
                    {
                        create_sql += $" [{y.colname}] {getdbcoltype(y)},";
                    });
                    var key_cols = nowtemplateDBTableCol.Where(y => y.iskey).ToList();
                    if (key_cols?.Any() ?? false)
                    {
                        create_sql += $@" CONSTRAINT [PK_{x}] PRIMARY KEY CLUSTERED (";
                        key_cols.ForEach(y =>
                        {
                            create_sql += $"[{y.colname}] ASC,";
                        });
                        create_sql = create_sql.TrimEnd(",".ToCharArray());
                        create_sql += @")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];";
                    }
                    else
                    {
                        create_sql += ");";
                    }
                    db_struct.table_create_sql_dic.Add(x, create_sql);
                });
                //建立视图的sql
                db_struct.view_create_sql_dic = sql_query<KeyValuePair<string, string>>(db_info, @"
 SELECT M.definition AS [Value] ,
        O.[name] AS [Key]
 FROM   sys.objects O
        INNER JOIN sys.sql_modules M ON O.[object_id] = M.[object_id]
 WHERE  O.[type] IN ( 'V' );
").ToDictionary(x => x.Key, x => x.Value);

            }
            return db_struct;
        }

        public override void detach_move(db_info db_info, string path)
        {
            var default_db_info = new db_info() { db_link_info = db_info.db_link_info };//切换为默认数据库连接，防止占用数据库无法分离

            //查询文件目录
            var sourceFilePath = sql_query<string>(default_db_info, $"select filename from master..sysdatabases where name = '{db_info.name}'").FirstOrDefault();
            if (string.IsNullOrEmpty(sourceFilePath))
            {
                db_info.updatestatus = 0;
                db_info.remark = $"数据库文件路径{sourceFilePath}不存在";
                return;
            }
            var nowCount = sql_query<int>(default_db_info, $"select count(1) from sys.databases where name = '{db_info.name}'").FirstOrDefault();
            if (nowCount <= 0)
            {
                db_info.updatestatus = 0;
                db_info.remark = $"数据库不存在";
                return;
            }
            //删除所有会话
            var killspid_list = sql_query<string>(default_db_info, $@"SELECT  'kill ' + CAST(spid AS VARCHAR(20)) FROM    sys.[sysprocesses] WHERE   DB_NAME([dbid]) = '{db_info.name}'")?.ToList();
            if (killspid_list?.Any() ?? false)
            {
                sql_exec(default_db_info, string.Join(';', killspid_list));
            }
            //分离数据库
            sql_exec(default_db_info, $"exec sp_detach_db '{db_info.name}'");
            db_info.updatestatus = 1;
            db_info.remark = "分离完成";
            //复制数据库文件
            //-mdf文件
            File.Move(sourceFilePath, Path.Combine(path, Path.GetFileName(sourceFilePath)));
            sourceFilePath = sourceFilePath.Replace(".mdf", "_log.ldf");
            //-ldf文件
            File.Move(sourceFilePath, Path.Combine(path, Path.GetFileName(sourceFilePath)));
            db_info.remark = "复制完成";
        }

        public override void diff_fix(db_info db_info, List<db_diff_info> list)
        {
            var exec_list = list?.Where(x => x.status != "修复成功")?.ToList();
            if (list?.Any() ?? false)
            {
                list.ForEach(x =>
                {
                    x.status = "开始修复";
                    var sqls = Regex.Split(x.fixsql, "\r\n[G|g][O|o][\r\n]*");//数据库本身不支持GO，在这里进行替换分割
                    foreach (var itemsql in sqls)
                    {
                        if (string.IsNullOrEmpty(itemsql.Trim()))
                        {
                            continue;
                        }
                        sql_exec(db_info, itemsql, null, 10 * 60);//设置10分钟超时
                    }
                    x.status = "修复成功";
                });
            }
        }

        public override void link_test(db_link_info db_link_info)
        {
            var db_test = new model.db_info()
            {
                db_link_info = db_link_info,
                name = "master"
            };
            sql_exec(db_test, "select top 1 1 FROM MASTER.DBO.SYSDATABASES");
        }

        public override List<db_diff_info> diff_check(db_struct template_db_struct, db_info db_info)
        {
            var res = new List<db_diff_info>();
            //获取目标数据库结构
            var db_struct = db_struct_get(db_info);
            //比较表差异
            var except_table_list = template_db_struct.table_list.Except(db_struct.table_list).Distinct().ToList() ?? new List<string>();
            if (except_table_list?.Any() ?? false)
            {
                except_table_list.ForEach(y =>
                {
                    res.Add(new db_diff_info()
                    {
                        no = Guid.NewGuid().ToString("N"),
                        targetdb = db_info.name,
                        createtime = DateTime.Now,
                        diffchangepre = "",
                        diffchangeafter = y,
                        diffcol = "",
                        difftable = y,
                        difftype = "表不存在",
                        remark = "",
                        fixsql = template_db_struct.table_create_sql_dic[y],
                        status = "待修复"
                    });
                });
            }

            //比较表字段差异(表不在的不再比较字段)
            var intersect_table_list = template_db_struct.table_list.Intersect(db_struct.table_list).Distinct().ToList() ?? new List<string>();
            intersect_table_list?.ForEach(y =>
            {
                var nowtemplatedbtablecol = template_db_struct.db_table_col_info_list.Where(z => z.tablename == y).ToList();
                nowtemplatedbtablecol.ForEach(z =>
                {
                    var templatecoltype = getdbcoltype(z);//字段类型
                                                          //--字段不存在
                    var nowtempdbtablecol = template_db_struct.db_table_col_info_list.FirstOrDefault(t => t.tablename == z.tablename && t.colname == z.colname);
                    if (nowtempdbtablecol == null)
                    {
                        res.Add(new db_diff_info()
                        {
                            no = Guid.NewGuid().ToString("N"),
                            targetdb = db_info.name,
                            createtime = DateTime.Now,
                            diffchangepre = "",
                            diffchangeafter = z.colname,
                            diffcol = z.colname,
                            difftable = y,
                            difftype = "字段不存在",
                            remark = "",
                            fixsql = $"ALTER TABLE {y} Add {z.colname} {templatecoltype};",
                            status = "待修复"
                        });
                    }
                    else
                    {
                        //--字段类型不一致
                        var tempColType = getdbcoltype(nowtempdbtablecol);//字段类型
                        if (templatecoltype != tempColType)
                        {
                            res.Add(new db_diff_info()
                            {
                                no = Guid.NewGuid().ToString("N"),
                                targetdb = db_info.name,
                                createtime = DateTime.Now,
                                diffchangepre = tempColType,
                                diffchangeafter = templatecoltype,
                                diffcol = z.colname,
                                difftable = y,
                                difftype = "字段类型不一致",
                                remark = "",
                                fixsql = $"ALTER TABLE {y} Alter COLUMN {z.colname} {templatecoltype};",
                                status = "待修复"
                            });
                        }
                    }

                    //-字段默认值不一致
                    if (!string.IsNullOrEmpty(z.coldefault)
                && (nowtempdbtablecol == null || nowtempdbtablecol.coldefault != z.coldefault))
                    {
                        var constraintsql = "";
                        if (!string.IsNullOrEmpty(nowtempdbtablecol?.colconstraint))
                        {
                            //--不为空先移除索引，再新增
                            constraintsql = $@" ALTER TABLE [{y}] DROP CONSTRAINT {nowtempdbtablecol.colconstraint};";
                        }
                        res.Add(new db_diff_info()
                        {
                            no = Guid.NewGuid().ToString("N"),
                            targetdb = db_info.name,
                            createtime = DateTime.Now,
                            diffchangepre = (nowtempdbtablecol?.coldefault) ?? "",
                            diffchangeafter = z.coldefault,
                            diffcol = z.colname,
                            difftable = y,
                            difftype = "字段默认值不一致",
                            remark = "",
                            fixsql = constraintsql + $" alter table {y} add constraint {z.colconstraint} default { z.coldefault} for {z.colname};",
                            status = "待修复"
                        });
                    }
                });
            });

            //比较表字段描述差异
            //-模板库表描述不为空，目标库表描述为空或目标库没有表
            var nullTableNametempDBTableCol = db_struct.db_table_col_info_list.Where(t => string.IsNullOrEmpty(t.tablenamedesc)).Select(x => x.tablename).ToList().Union(except_table_list).Distinct();
            template_db_struct.db_table_col_info_list.Where(y => !string.IsNullOrEmpty(y.tablenamedesc) && nullTableNametempDBTableCol.Any(t => t == y.tablename))
                                  .Select(x => new { x.tablename, x.tablenamedesc })
                                  ?.Distinct()
                                  ?.ToList()
                                  ?.ForEach(y =>
                                  {
                                      res.Add(new db_diff_info()
                                      {
                                          no = Guid.NewGuid().ToString("N"),
                                          targetdb = db_info.name,
                                          createtime = DateTime.Now,
                                          diffchangepre = "",
                                          diffchangeafter = y.tablenamedesc,
                                          diffcol = "",
                                          difftable = y.tablename,
                                          difftype = "表描述不存在",
                                          remark = "",
                                          fixsql = $" EXECUTE sp_addextendedproperty N'MS_Description', N'{y.tablenamedesc.Replace("'", "''")}', N'SCHEMA', N'dbo',N'table',N'{y.tablename}'; ",
                                          status = "待修复"
                                      });
                                  });

            //-字段描述数据比较
            //--模板库字段描述不为空，目标库字段描述为空或目标库没有表
            var nullcolnametempdbtablecol = db_struct.db_table_col_info_list.Where(t => string.IsNullOrEmpty(t.coldesc)).ToList();
            template_db_struct.db_table_col_info_list.Where(y => !string.IsNullOrEmpty(y.coldesc) &&
                                    (except_table_list.Any(t => t == y.tablename) || nullcolnametempdbtablecol.Any(t => t.tablename == y.tablename && t.colname == y.colname)))
                              ?.Distinct()
                              ?.ToList()
                              ?.ForEach(y =>
                              {
                                  res.Add(new db_diff_info()
                                  {
                                      no = Guid.NewGuid().ToString("N"),
                                      targetdb = db_info.name,
                                      createtime = DateTime.Now,
                                      diffchangepre = "",
                                      diffchangeafter = y.coldesc,
                                      diffcol = y.colname,
                                      difftable = y.tablename,
                                      difftype = "字段描述不存在",
                                      remark = "",
                                      fixsql = $" EXECUTE sp_addextendedproperty N'MS_Description', N'{y.coldesc.Replace("'", "''")}', N'SCHEMA', N'dbo', N'TABLE', N'{y.tablename}',N'COLUMN',N'{y.colname}'; ",
                                      status = "待修复"
                                  });
                              });

            //比较视图字段差异
            //--视图不存在直接新增
            //--视图存在字段不匹配的，直接删除视图新建
            var exceptviewlist = template_db_struct.view_col_dic.Select(x => x.Key).Except(db_struct.view_col_dic.Select(x => x.Key)).ToList();
            var addView = new HashSet<string>();
            exceptviewlist?.ForEach(v =>
            {
                var y = template_db_struct.view_col_dic.FirstOrDefault(z => z.Key == v).Value;
                if (addView.Contains(y))
                {
                    return;
                }
                addView.Add(y);
                var viewSql = $@"IF EXISTS ( SELECT  *
                                        FROM    sys.views
                                        WHERE   object_id = OBJECT_ID(N'[dbo].[{y}]') )
                                           BEGIN
                                               DROP VIEW [dbo].[{y}]
                                           END;
GO
{template_db_struct.view_create_sql_dic[y]}";
                res.Add(new db_diff_info()
                {
                    no = Guid.NewGuid().ToString("N"),
                    targetdb = db_info.name,
                    createtime = DateTime.Now,
                    diffchangepre = "",
                    diffchangeafter = "",
                    diffcol = "",
                    difftable = y,
                    difftype = "视图不一致",
                    remark = "",
                    fixsql = viewSql,
                    status = "待修复"
                });
            });
            return res;
        }

        /// <summary>
        /// 获取字段类型
        /// </summary>
        /// <param name="dbTableColInfo"></param>
        /// <returns></returns>
        private string getdbcoltype(db_table_col_info db_table_col_info)
        {
            var coltype = "";
            switch (db_table_col_info.coltype)
            {
                case "nchar":
                case "char":
                case "nvarchar":
                case "varchar":
                case "binary":
                case "datetime2":
                case "datetimeoffset":
                case "varbinary":
                    var length = db_table_col_info.collength.ToString();
                    if (db_table_col_info.collength == -1)
                    {
                        length = "max";
                    }
                    coltype = $"{db_table_col_info.coltype}({length})";
                    break;
                case "decimal":
                case "numeric":
                    coltype = $"{db_table_col_info.coltype}({db_table_col_info.collength},{db_table_col_info.colsclength})";
                    break;
                case "time":
                    coltype = $"{db_table_col_info.coltype}({db_table_col_info.colsclength})";
                    break;
                default:
                    coltype = $"{db_table_col_info.coltype}";
                    break;
            }
            //自增
            if (db_table_col_info.istip)
            {
                coltype += " identity(1, 1) ";
            }
            //空值
            if (db_table_col_info.colisnull)
            {
                coltype += " null";
            }
            else
            {
                coltype += " not null";
            }
            return coltype;
        }
    }
}
